<!DOCTYPE html>
<html lang="zh-cn">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>openGauss Blog  | openGauss1.0.1支持oracle-fdw和mysql-fdw</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">

    
    <link rel="shortcut icon" href="https://xzx666.gitee.io/lookeng/img/favicon.ico" type="image/x-icon" />
    <link rel="apple-touch-icon" href="https://xzx666.gitee.io/lookeng/img/apple-touch-icon.png" />

    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

    
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
    
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/commen.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/blog.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/mobile.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/pagination.css?t=1607593672000">
    
    <script>
      var _hmt = _hmt || [];
      (function() {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?34e9f585f29581007941aa1698181871";
        var s = document.getElementsByTagName("script")[0]; 
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
</head>
<body>
  <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
  <script src='//unpkg.com/valine/dist/Valine.min.js'></script>






<div class="container">
    <div class="columns blog-detail">
        <div class="post_detail">
            <div class="is-child box">
                <div class="breadCrumb"><a href="\zh\">博客/</a></div>
                <div class="blog-detail-header">
                    <h1>openGauss1.0.1支持oracle-fdw和mysql-fdw</h1>
                    <div class="blog-detail-prop">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-date.png">
                        <span class="article_right_date">2020-11-19</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-author.png">
                        <span class="article_right_author">多米爸比</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-view.png">
                        <span id="/zh/post/duomibabi/opengauss1-0-1%E6%94%AF%E6%8C%81oracle-fdw%E5%92%8Cmysql-fdw/" class="leancloud_visitors" data-flag-title="openGauss1.0.1支持oracle-fdw和mysql-fdw">
                            <i class="leancloud-visitors-count"></i>                     
                        </span>
                    </div>
                    <div class="blog-detail-tags">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-tag.png">
                        
                        <a href="https://xzx666.gitee.io/lookeng/zh/tags/opengauss1.0.1%E6%94%AF%E6%8C%81oracle-fdw%E5%92%8Cmysql-fdw">openGauss1.0.1支持oracle-fdw和mysql-fdw</a>
                        
                    </div>
                </div>
                <div class="content blog-content">
                    

<h1 id="opengauss1-0-1支持oracle-fdw和mysql-fdw-a-name-zh-cn-topic-0291959517-a">openGauss1.0.1支持oracle-fdw和mysql-fdw<a name="ZH-CN_TOPIC_0291959517"></a></h1>

<p>FDW(Foreign Data Wrappers)插件允许在openGauss里访问其他异构数据库的表，openGauss支持Foreign Data Wrappers for oracle （oracle_fdw），Foreign Data Wrappers for MySQL（mysql_fdw）和Foreign Data Wrappers for PostgreSQL（Postgres_fdw），从而支持在openGauss中访问异构其他数据库。</p>

<p>使用postgres_fdw插件不需要重新编译openGauss，具有系统管理员权限用户直接使用create extension创建扩展组件，普通用户即可create server配置异构数据库连接参数，create user mapping创建异构用户映射关系，CREATE FOREIGN TABLE创建指定数据库的外表。</p>

<p>使用oracle_fdw和mysql_fdw插件需要安装相应数据库的客户端包，同时需要重新编译openGauss，在configure时配置enable_mysql_fdw和enable_oracle_fdw。数据库里创建扩展与上面使用postgres_fdw类似。</p>

<h2 id="数据库客户端包安装-a-name-zh-cn-topic-0292870896-a">数据库客户端包安装<a name="ZH-CN_TOPIC_0292870896"></a></h2>

<h3 id="mysql-mariadb-头文件-a-name-section1110445413510-a">mysql(mariadb)头文件<a name="section1110445413510"></a></h3>

<p>openGauss源码编译开启enable_mysql_fdw需要依赖头文件mariadb_com.h。</p>

<p>查找资料得知mariadb_com.h在这个包下：</p>

<p><a href="http://repo.okay.com.mx/centos/7/x86_64/release/mariadb-connector-c-devel-3.0.10-1.el7.x86_64.rpm">mariadb-connector-c-devel-3.0.10-1.el7.x86_64.rpm</a></p>

<pre><code># rpm -ql mariadb-connector-c-devel-3.0.10-1.el7 |grep mariadb_com.h
/usr/include/mysql/mariadb_com.h
</code></pre>

<p>Centos7.6下顺藤摸瓜按依赖安装。</p>

<pre><code># rpm -ivh MariaDB-common-5.5.68-1.el7.centos.x86_64.rpm
# rpm -ivh crypto-policies-20170816-1.git2618a6c.el7.noarch.rpm
# rpm -ivh openssl11-libs-1.1.0i-1.el7.x86_64.rpm
# rpm -ivh mariadb-connector-c-3.0.10-1.el7.x86_64.rpm
# rpm -ivh mariadb-connector-c-devel-3.0.10-1.el7.x86_64.rpm
</code></pre>

<h3 id="oracle客户端包-a-name-section16486454949-a">oracle客户端包<a name="section16486454949"></a></h3>

<pre><code># yum install oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm  
# yum install oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm  
</code></pre>

<p>安装完上面两个包后，修改下动态库环境变量。</p>

<pre><code># vi /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/lib/oracle/11.2/client64/lib

# ldconfig
</code></pre>

<h2 id="opengauss-sever源码编译-a-name-zh-cn-topic-0292870897-a">openGauss-sever源码编译<a name="ZH-CN_TOPIC_0292870897"></a></h2>

<h3 id="configure配置enable-mysql-fdw和enable-oracle-fdw-a-name-section639984215911-a">configure配置enable_mysql_fdw和enable_oracle_fdw<a name="section639984215911"></a></h3>

<pre><code>./configure  --prefix=/opt/og \
--gcc-version=8.2.0 \
--3rd=/opt/binarylibs \
--with-readline \
--with-zlib \
--with-libxml \
--enable-mysql-fdw \
--enable-oracle-fdw \
--enable-thread-safety \
CC=g++ CFLAGS=&quot;-O2 -g3&quot;
</code></pre>

<h3 id="make-a-name-section13604621205-a">make<a name="section13604621205"></a></h3>

<pre><code>make -sj 
make -sj install 
</code></pre>

<h3 id="初始化-a-name-section44331324516-a">初始化<a name="section44331324516"></a></h3>

<pre><code>/opt/og/bin/gs_initdb --nodename=og_6432 \
--pgdata=/opt/ogdata \
--encoding=UTF-8 \
--locale=en_US.UTF-8 \
--username=omm \
--pwpasswd=Enmotech@2020 \
--security

vi /opt/ogdata/postgresql.conf
port=6432
listen_addresses = '0.0.0.0'
password_encryption_type = 0  
</code></pre>

<h3 id="启动服务-a-name-section32349302120-a">启动服务<a name="section32349302120"></a></h3>

<pre><code>/opt/og/bin/gs_ctl start -D /opt/ogdata -l og_server.log &amp; 
</code></pre>

<h3 id="创建用户-a-name-section144840382117-a">创建用户<a name="section144840382117"></a></h3>

<pre><code>create user postgres sysadmin IDENTIFIED BY 'Enmotech@2020';
create user opengauss IDENTIFIED BY 'Enmotech@2020';
</code></pre>

<p>postgres用户具有管理权限，而已用来创建extension及分配普通用户使用fdw的权限openGauss普通用户可以创建server及使用外部表。</p>

<h2 id="fdw测试-a-name-zh-cn-topic-0292870898-a">FDW测试<a name="ZH-CN_TOPIC_0292870898"></a></h2>

<h3 id="mysql-fdw测试-a-name-section7112312194912-a">mysql_fdw测试<a name="section7112312194912"></a></h3>

<p>创建扩展(用户必须有sysadmin权限)。</p>

<pre><code>$ gsql -p6432 -Upostgres postgres

postgres=&gt;  create extension mysql_fdw with schema public;
CREATE EXTENSION
</code></pre>

<p>查看扩展版本。</p>

<pre><code>postgres=&gt; select mysql_fdw_version();
 mysql_fdw_version 
-------------------
             20503
(1 row)
</code></pre>

<p>postgres用户(有sysadmin管理权限)赋予普通用户openGauss使用mysql_fdw权限。</p>

<pre><code>postgres=&gt; grant USAGE on FOREIGN data wrapper mysql_fdw to opengauss;
GRANT
</code></pre>

<p>普通用户openGauss操作创建server。</p>

<pre><code>postgres=&gt; create server server_mysql foreign data wrapper mysql_fdw options(host'172.19.0.100',port '3306');
CREATE SERVER
</code></pre>

<p>普通用户openGauss操作创建用户映射。</p>

<pre><code>postgres=&gt; create user mapping for opengauss server server_mysql options(username 'root',password '123456');
CREATE USER MAPPING
</code></pre>

<p>普通用户openGauss创建外部表。</p>

<pre><code>postgres=&gt; create foreign table f_mysql_t1(
id int
)server server_mysql 
options (dbname 'mysql',table_name 't1');
CREATE FOREIGN TABLE
</code></pre>

<p>通过外部表查询mysql数据库表数据。</p>

<pre><code>postgres=&gt; select * from f_mysql_t1;
  id  
------
 1001
(1 row)
</code></pre>

<p>从openGauss端写入数据到mysql。</p>

<pre><code>postgres=&gt; insert into f_mysql_t1 values(1002);
INSERT 0 1
</code></pre>

<p>注意mysql端表必须有主键或唯一索引，否则会报错。</p>

<pre><code>postgres=&gt; insert into f_mysql_t1 values(1002);
ERROR:  first column of remote table must be unique for INSERT/UPDATE/DELETE operation
</code></pre>

<p>再次查看数据。</p>

<pre><code>postgres=&gt; select * from f_mysql_t1;
  id  
------
 1001
 1002
(2 rows)
</code></pre>

<h3 id="oracle-fdw测试-a-name-section5483540194816-a">oracle_fdw测试<a name="section5483540194816"></a></h3>

<p>与mysql_fdw类似，注意LD_LIBRARY_PATH配置了oracle的lib路径（/usr/lib/oracle/11.2/client64/lib）创建扩展(用户必须有sysadmin权限)。</p>

<pre><code>$ gsql -p6432 -Upostgres postgres

postgres=#  create extension oracle_fdw with schema public;
CREATE EXTENSION
</code></pre>

<p>查看扩展版本。</p>

<pre><code>postgres=&gt; select oracle_diag();
                         oracle_diag                          
--------------------------------------------------------------
 oracle_fdw 2.2.0, PostgreSQL 9.2.4, Oracle client 11.2.0.4.0
(1 row)
</code></pre>

<p>postgres用户(有sysadmin管理权限)赋予普通用户opengauss使用oracle_fdw权限。</p>

<pre><code>postgres=&gt; grant USAGE on FOREIGN data wrapper oracle_fdw to opengauss;
GRANT
</code></pre>

<p>普通用户openGauss操作创建server。</p>

<pre><code>$ gsql -p6432 -Uopengauss postgres

postgres=&gt; create server server_oracle foreign data wrapper oracle_fdw options(dbserver '172.17.0.2:1521/lee');
CREATE SERVER
</code></pre>

<p>普通用户openGauss操作创建用户映射。</p>

<pre><code>postgres=&gt; create user mapping for opengauss server server_oracle options(user 'system',password 'admin');
CREATE USER MAPPING
</code></pre>

<p>普通用户openGauss创建外部表。</p>

<pre><code>postgres=&gt; create foreign table f_oracle_t2(
id int
)server server_oracle 
OPTIONS (
    schema 'SYSTEM',
    &quot;table&quot; 'T2'
);
CREATE FOREIGN TABLE
</code></pre>

<p>通过外部表查询oracle数据库t2表数据。</p>

<pre><code>postgres=&gt; select * from f_oracle_t2;
  id  
------
 2001
(1 row)
</code></pre>

<p>从openGauss端写入数据到oracle数据库t2表。</p>

<pre><code>postgres=&gt; insert into f_oracle_t2 values(2002);
INSERT 0 1
</code></pre>

<p>再次查看数据。</p>

<pre><code>postgres=&gt; select * from f_oracle_t2 ;
  id  
------
 2001
 2002
(2 rows)
</code></pre>

                </div>
            </div>
            <div class="box" style="margin-top:15px;">
                <div style="font-size:14px;color:gray"><strong>【免责声明】</strong>本文仅代表作者本人观点，与本网站无关。本网站对文中陈述、观点判断保持中立，不对所包含内容的准确性、可靠性或完整性提供任何明示或暗示的保证。本文仅供读者参考，由此产生的所有法律责任均由读者本人承担。</div>
            </div>
            <div class="post-comment">
                
                  
  <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/custom.css" />
  <div id="vcomments" lang="zh-cn" mess="说点什么吧..."></div>

  <script type="text/javascript">
    const placeholder = document.getElementById("vcomments").getAttribute("mess"),
          lang = document.getElementById("vcomments").getAttribute("lang");
    const  langs = lang == "zh-cn" ? "zh-cn" : "en";
    new Valine({
        el: '#vcomments' ,
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm', 
        placeholder: placeholder,
        visitor: true,
		    meta: ['nick','mail','link'],
        lang: langs
    });
  </script>
            </div>
        </div>
    </div>
</div>
<script>
    $(function (){
        var query = new AV.Query('Counter');
        query.equalTo('url', decodeURIComponent('\/zh\/post\/duomibabi\/opengauss1-0-1%E6%94%AF%E6%8C%81oracle-fdw%E5%92%8Cmysql-fdw\/'));
        query.find().then(function (data) {
            if(data.length && (data[0].attributes.author === 'openGauss')){
                var counter = AV.Object.createWithoutData('Counter', data[0].id);
                counter.set('author', '多米爸比');
                counter.save();
            }
        })
    })
</script>







<input id="iframeUrl" type="text" style="display: none;" value=https://xzx666.gitee.io/>




<script defer src="https://xzx666.gitee.io/lookengjs/all.js"></script>
<script src="https://xzx666.gitee.io/lookengjs/flexible.js"></script>
<script>
  const hash = window.location.search,
        pageurl = window.location.href;
  var langss = document.querySelector("html").lang === "zh-cn" ? "zh" : "en";
  console.log("hash:",hash,"pageurl:",pageurl,"ttr",pageurl.split(langss + "/")[1])

  if(!document.getElementById("vcomments")) {
    new Valine({
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm',
        visitor: true,
		    meta: ['nick','mail','link']
    });
  }
 
  
    


    const url = document.getElementById("iframeUrl").value;

    function observe (el, options, callback) {
      var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver
      var observer = new MutationObserver(callback)
      observer.observe(el, options)
    }

    var options = {
        childList: true,
        subtree: true,
        characterData: true
      }
    observe(document.body, options, (records, instance) => {
    const height = document.body.scrollHeight;
    parent.postMessage(height,url);
    })
    if(pageurl.split(langss + "/")[1]) parent.postMessage(pageurl.split(langss + "/")[1],url);
    

    if(document.querySelector("#notFound")) parent.postMessage("我404了",url);

</script>
</body>
</html>
